import pymysql
import pandas as pd
from sqlalchemy import Column, String, Integer, DateTime
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import or_
import time

"""
connection config & engine for sqlalchemy
"""
connect_info = 'mysql+pymysql://root:123456@192.168.141.4:3306/gallery?charset=utf8'
engine = create_engine(connect_info,
                    echo=False,pool_size=100, pool_recycle=100, pool_pre_ping=True)  #use sqlalchemy to build link-engine
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)
# 创建对象的基类:
Base = declarative_base()


class ImageInfo(Base):
    # 表的名字:
    __tablename__ = 'crawled_image'
    # 表的结构:
    id = Column(Integer(), primary_key=True, autoincrement=True)
    url = Column(String(2000))
    image_print = Column(String(2000))
    crawled_time = Column(DateTime())
    parent_url = Column(String(2000))
    abbr_parent_url = Column(String(2000))
    index = Column(Integer())

class DBOperator:
    # def __init__(self):
    #     self.session = DBSession()

    def add_image_info(self, image_info):
        session = DBSession()
        # 添加到session:
        session.add(image_info)
        # 提交即保存到数据库:
        session.commit()
        # # 关闭session:
        session.close()

    def check_image_info(self, image_info):
        session = DBSession()
        # 添加到session:
        result = session.query(ImageInfo).filter((ImageInfo.url == image_info.url) |
                                                 (ImageInfo.image_print == image_info.image_print)).all()
        return result

    def check_parent_url(self, parent_url):
        session = DBSession()
        # 添加到session:
        # 添加到session:
        result = session.query(ImageInfo).filter(ImageInfo.parent_url == parent_url).all()
        return result

    def check_abbr_parent_url(self, abbr_parent_url):
        session = DBSession()
        # 添加到session:
        result = session.query(ImageInfo).filter(ImageInfo.abbr_parent_url == abbr_parent_url).all()
        return result

    # def insert_image_info(self, image_info):
    #
    #     result = self.session.add(image_info)
    #     self.session.commit
    #     return result

if __name__ == '__main__':
    db_session = DBOperator()
    image_info = ImageInfo()
    image_info.url = 'https://p1.toutiaoimg.com/img/tos-cn-i-0022/923ed1b199e84fafb484208cbead2500~tplv-obj:1800:1348.image?from=post'
    image_info.image_print = 'e63e1e1abe34b570'
    image_info.crawled_time = time
    # print(db_session.add_image_info(image_info))
    res = db_session.check_image_info(image_info)
    print(len(res))
    # for item in res:
    #     print(item.url + "..." + item.image_print)
    # # print(res)
    # url = 'https://www.toutiao.com/w/i1694037209288718/'
    # print(url.find('?'))
    # print(url[:url.find('?')])
    # res = db_session.check_abbr_parent_url(url[:url.find('?')])
    # for item in res:
    #     print(item.url + "..." + item.image_print)



